*%INCLUDE "~/jim/gzipseq.mcr";

***********************************************************************
READ HEALTH COST DATA FROM CRISTOBAL 

NOTE, TO ADD A YEAR USING MEDICAL CPI GO TO 
N:\Private\Poverty Trends\SAS\health_insurance_72_**.sas
***********************************************************************;
	
DATA cristobal;
SET alldat.health_insurance72_17(RENAME=(code=state2 year=ref_year));

* WEIGHTS FOR MISSING/RECODED/SUPRESSED STATES, FROM STATE_ID_CROSSWALK_8081.XLS;
IF state2=4 THEN st_wgt=0.0275;
ELSE IF state2=5 THEN st_wgt=0.0143;
ELSE IF state2=6 THEN st_wgt=0.1815;
ELSE IF state2=11 THEN st_wgt=0.0031;
ELSE IF state2=12 THEN st_wgt=0.0856;
ELSE IF state2=13 THEN st_wgt=0.0439;
ELSE IF state2=17 THEN st_wgt=0.0665;
ELSE IF state2=18 THEN st_wgt=0.0326;
ELSE IF state2=19 THEN st_wgt=0.0157;
ELSE IF state2=20 THEN st_wgt=0.0144;
ELSE IF state2=23 THEN st_wgt=0.0068;
ELSE IF state2=26 THEN st_wgt=0.0533;
ELSE IF state2=27 THEN st_wgt=0.0264;
ELSE IF state2=28 THEN st_wgt=0.0152;
ELSE IF state2=29 THEN st_wgt=0.03;
ELSE IF state2=32 THEN st_wgt=0.0107;
ELSE IF state2=33 THEN st_wgt=0.0066;
ELSE IF state2=35 THEN st_wgt=0.0097;
ELSE IF state2=36 THEN st_wgt=0.1017;
ELSE IF state2=37 THEN st_wgt=0.0431;
ELSE IF state2=39 THEN st_wgt=0.0608;
ELSE IF state2=40 THEN st_wgt=0.0185;
ELSE IF state2=41 THEN st_wgt=0.0183;
ELSE IF state2=46 THEN st_wgt=0.004;
ELSE IF state2=47 THEN st_wgt=0.0305;
ELSE IF state2=51 THEN st_wgt=0.0379;
ELSE IF state2=53 THEN st_wgt=0.0316;
ELSE IF state2=54 THEN st_wgt=0.0097;
ELSE st_wgt=0;

RUN;

PROC SORT DATA=cristobal; 
   BY ref_year;
RUN;
PROC MEANS DATA=cristobal; 
   BY ref_year;
   VAR medicare mcaid_aged mcaid_adults mcaid_child empl_health_ind_3yravg 
       empl_health_fam_3yravg empl_health_ind empl_health_fam; 
   WEIGHT st_wgt; 
   output out=st_avg MEAN=;
RUN;

DATA st_avg;
   SET st_avg(KEEP=ref_year medicare mcaid_aged mcaid_adults mcaid_child empl_health_ind_3yravg
       empl_health_fam_3yravg empl_health_ind empl_health_fam);

state2=99;
RUN;

DATA cristobal;
   SET cristobal(DROP=region state) st_avg;
RUN;

PROC SORT DATA=cristobal; 
   BY ref_year state2;
RUN;

************************************************************************
READ AND FORMAT PARTIAL MASTER DATA
************************************************************************;
DATA master_temp1;
   SET alldat.cons_exp80_17(KEEP=srv_year newid ref_year perslt18 persot64 fam_size
                          qyear state1 region finlwt);

IF srv_year GE 84 THEN state2=state1;
* RECODE STATE VALUES FOR 80-81, SEE STATE_ID_CROSSWALK_8081.XLS;
IF 80 LE srv_year LE 81 THEN DO;
   IF state1=63 THEN state2=1;
	ELSE IF state1=94 THEN state2=2;
	ELSE IF state1=86 THEN state2=4;
	ELSE IF state1=71 THEN state2=5;
	ELSE IF state1=93 THEN state2=6;
	ELSE IF state1=84 THEN state2=8;
	ELSE IF state1=16 THEN state2=9;
	ELSE IF state1=53 THEN state2=11;
	ELSE IF state1=59 THEN state2=12;
	ELSE IF state1=58 THEN state2=13;
	ELSE IF state1=95 THEN state2=15;
	ELSE IF state1=33 THEN state2=17;
	ELSE IF state1=32 THEN state2=18;
	ELSE IF state1=42 THEN state2=19;
	ELSE IF state1=47 THEN state2=20;
	ELSE IF state1=61 THEN state2=21;
	ELSE IF state1=72 THEN state2=22;
	ELSE IF state1=11 THEN state2=23;
	ELSE IF state1=52 THEN state2=24;
	ELSE IF state1=14 THEN state2=25;
	ELSE IF state1=34 THEN state2=26;
	ELSE IF state1=41 THEN state2=27;
	ELSE IF state1=64 THEN state2=28;
	ELSE IF state1=43 THEN state2=29;
	ELSE IF state1=81 THEN state2=91;
	ELSE IF state1=46 THEN state2=31;
	ELSE IF state1=22 THEN state2=34;
	ELSE IF state1=85 THEN state2=35;
	ELSE IF state1=21 THEN state2=36;
	ELSE IF state1=56 THEN state2=37;
	ELSE IF state1=31 THEN state2=39;
	ELSE IF state1=92 THEN state2=41;
	ELSE IF state1=23 THEN state2=42;
	ELSE IF state1=15 THEN state2=93;
	ELSE IF state1=57 THEN state2=45;
	ELSE IF state1=62 THEN state2=47;
	ELSE IF state1=74 THEN state2=48;
	ELSE IF state1=87 THEN state2=49;
	ELSE IF state1=54 THEN state2=51;
	ELSE IF state1=91 THEN state2=53;
	ELSE IF state1=55 THEN state2=54;
	ELSE IF state1=35 THEN state2=55;
	ELSE state2=99;
END;
RUN;

PROC SORT data=master_temp1;
   BY region srv_year;
RUN;
PROC MEANS data=master_temp1;
   BY region srv_year;
   VAR fam_size;
   WEIGHT finlwt; 
   output out=fsize_avg MEAN=fsize_bar;
RUN;

DATA master_temp1;
   MERGE master_temp1(IN=in1) fsize_avg(KEEP=region srv_year fsize_bar);
   BY region srv_year;
RUN;

PROC SORT data=master_temp1;
   BY newid srv_year;
RUN;

PROC SORT data=alldat.all_health8017;
   BY newid srv_year;
RUN;

************************************************************************************
MERGE PARTIAL MASTER, AVG FAMILY SIZE, AND CE DETAILED HEALTH DATA
************************************************************************************;

DATA master_temp1;
   MERGE master_temp1(IN=in1) 
	 alldat.all_health8017(DROP=qyear);
   BY newid srv_year;
   IF in1;

*******************************************************
FORMAT DATA FROM CE HEALTH EXPENDITURE FILES
*******************************************************;
* IF num_pol is not missing but num_cov=. then total is coded as missing;
IF num_pol NE . AND num_cov NE . THEN tot_private=num_cov;
   ELSE IF num_pol NE . AND num_cov=. THEN tot_private=.;
   ELSE IF num_pol=. THEN tot_private=0;

* THIS WILL CODE AS MISSING THOSE THAT SAY THEY HAVE A MEDICAID ENROLLEE IN THE
  CU(mdcdenr=1), BUT NUMBER ON MEDICAID IS MISSING;
IF mcaid_tot GT 0 THEN tot_medicaid=mcaid_tot;
   ELSE IF mdcdenr IN(2,.) OR mcaid_tot=0 THEN tot_medicaid=0;
IF mcaresum GT 0 THEN tot_medicare=mcaresum;
   ELSE IF hhmcrenr IN(2,.) OR mcaresum=0 THEN tot_medicare=0;

RUN;

PROC SORT data=master_temp1;
   BY ref_year state2;
RUN;

DATA master_temp1;
   MERGE master_temp1(IN=in1) 
	 cristobal;
   BY ref_year state2;
   IF in1;

****************************************************************
* RECODES
* NOTE: WE TREAT ALL 65+ AS ENROLLED IN MEDICARE
****************************************************************;

adults=fam_size-perslt18-persot64;

*ASSUME THAT OTHER COVERAGE (I.E. MILITARY) IS FOR A SINGLE INDIVIDUAL;
*NOTE IN 2015:1 THEY ADDED OTHMDCOV, WHICH IS NUM COVERED BY THESE PLANS. DON'T CURRENTLY USE THIS VAR;
IF oth_tot GT 0 THEN tot_private=tot_private+1;


*******************
MAY WANT TO CLEAN THIS UP A BIT FOR 80-81
*******************;

* HAVE MEDICAID TRUMP PRIVATE & MEDICARE;
IF srv_year GE 84 THEN DO;
IF tot_medicaid GE fam_size THEN mcaid_and_priv=tot_private;
   ELSE IF SUM(OF tot_medicaid tot_private) GT fam_size 
	THEN mcaid_and_priv=MIN(fam_size,MIN(tot_medicaid,tot_private));
   ELSE mcaid_and_priv=0;
END;

IF 80 LE srv_year LE 81 THEN DO;
   IF tot_medicaid GE fam_size THEN num_fam_plan2=0;
     ELSE num_fam_plan2=num_fam_plan;
   IF tot_medicaid GE fam_size THEN num_ind_plan2=0;
     ELSE num_ind_plan2=num_ind_plan;

   IF SUM(OF tot_medicaid num_ind_plan2) GT fam_size 
        THEN mcaid_and_priv=MIN(fam_size,MIN(tot_medicaid,num_ind_plan2));
   ELSE mcaid_and_priv=0; 

num_ind_plan2=num_ind_plan2-mcaid_and_priv;
END;

IF tot_medicaid GE fam_size THEN mcaid_and_mcare=persot64;
   ELSE IF SUM(OF tot_medicaid persot64) GT fam_size 
	THEN mcaid_and_mcare=MIN(fam_size,MIN(tot_medicaid,persot64));
   ELSE mcaid_and_mcare=0;

**********************
MEDIGAP
**********************;
*NOTE, IF HAVE MEDICAID & PRIVATE THEN ASSUME JUST MEDICAID;
IF srv_year GE 84 
   THEN tot_medigap=MIN(persot64,MAX(0,tot_private-mcaid_and_priv-adults-perslt18));
   ELSE IF 80 LE srv_year LE 81 AND SUM(OF persot64 num_ind_plan2) GT fam_size
      THEN tot_medigap=MIN(persot64,MAX(0,num_ind_plan2-adults-perslt18));
   ELSE IF 80 LE srv_year LE 81 AND persot64 GT 0 AND num_fam_plan2 GT 0
      THEN tot_medigap=persot64-mcaid_and_mcare;
   ELSE tot_medigap=0;

tot_private2=tot_private-tot_medigap-mcaid_and_priv;
tot_medicare2=persot64-mcaid_and_mcare;

IF srv_year=80 THEN medigap=421.3;
ELSE IF srv_year=81 THEN medigap=447;
ELSE IF srv_year=82 THEN medigap=474.3;
ELSE IF srv_year=83 THEN medigap=503.2;
ELSE IF srv_year=84 THEN medigap=534;
ELSE IF srv_year=85 THEN medigap=566.5;
ELSE IF srv_year=86 THEN medigap=601.1;
ELSE IF srv_year=87 THEN medigap=637.8;
ELSE IF srv_year=88 THEN medigap=676.7;
ELSE IF srv_year=89 THEN medigap=718;
ELSE IF srv_year=90 THEN medigap=689.5;
ELSE IF srv_year=91 THEN medigap=661;
ELSE IF srv_year=92 THEN medigap=980;
ELSE IF srv_year=93 THEN medigap=990.9;
ELSE IF srv_year=94 THEN medigap=1001.7;
ELSE IF srv_year=95 THEN medigap=1031.7;
ELSE IF srv_year=96 THEN medigap=1061.8;
ELSE IF srv_year=97 THEN medigap=1091.9;
ELSE IF srv_year=98 THEN medigap=1122;
ELSE IF srv_year=99 THEN medigap=1201;
ELSE IF srv_year=100 THEN medigap=1319.4;
ELSE IF srv_year=101 THEN medigap=1483;
ELSE IF srv_year=102 THEN medigap=1528;
ELSE IF srv_year=103 THEN medigap=1596.4;
ELSE IF srv_year=104 THEN medigap=1731.1;
ELSE IF srv_year=105 THEN medigap=1794;
ELSE IF srv_year=106 THEN medigap=1704.7;
*  THESE LAST THREE JUST USE 2006 # AND MED CARE CPI;
ELSE IF srv_year=107 THEN medigap=1704.7*1.044;
ELSE IF srv_year=108 THEN medigap=1704.7*1.083;
ELSE IF srv_year=109 THEN medigap=1704.7*1.117;
ELSE IF srv_year=110 THEN medigap=1704.7*1.202;  
ELSE IF srv_year=111 THEN medigap=1704.7*1.238;  
ELSE IF srv_year=112 THEN medigap=1704.7*1.284; 
ELSE IF srv_year=113 THEN medigap=1704.7*1.315;  
ELSE IF srv_year=114 THEN medigap=1704.7*1.347;  
ELSE IF srv_year=115 THEN medigap=1704.7*1.382;  
ELSE IF srv_year=116 THEN medigap=1704.7*1.435;  
ELSE IF srv_year=117 THEN medigap=1704.7*1.471;  
ELSE IF srv_year=118 THEN medigap=1704.7*1.497;  * THIS CPI IS JUST A PLACEHOLDER;



***************************
Value of private care
***************************;
IF 80 LE srv_year LE 81 THEN DO;
   IF num_fam_plan2 GT 0 AND fam_size GT 1 THEN pc_private=empl_health_fam_3yravg/fsize_bar;
   ELSE IF num_ind_plan2 GE fam_size AND fam_size GT 1 THEN pc_private=empl_health_fam_3yravg/fsize_bar;
   ELSE IF 1 LE num_ind_plan2 LE fam_size AND fam_size GT 1 
		THEN pc_private=empl_health_fam_3yravg/fsize_bar*num_ind_plan2/fam_size;
   ELSE IF SUM(OF num_ind_plan2 num_fam_plan2) GT 0 AND fam_size=1
		THEN pc_private=empl_health_ind_3yravg/fsize_bar;
   ELSE IF SUM(OF num_ind_plan2 num_fam_plan2)=0 
		THEN pc_private=0;
   ELSE IF num_ind_plan2=. AND num_fam_plan2=. THEN pc_private=0;
END;
IF srv_year GE 84 AND tot_private2 NE . THEN DO;
   IF tot_private2 GE fam_size AND fam_size GT 1 THEN pc_private=empl_health_fam_3yravg/fsize_bar;
   ELSE IF 1 LE tot_private2 LT fam_size AND fam_size GT 1 
		THEN pc_private=empl_health_fam_3yravg/fsize_bar*tot_private2/fam_size;
   ELSE IF tot_private2 GT 0 AND fam_size=1
		THEN pc_private=empl_health_ind_3yravg/fam_size;
   ELSE IF tot_private2=0 THEN pc_private=0;
END;

***************************
Value of medicare
***************************;
IF persot64 NE . THEN
	pc_medicare=medicare*tot_medicare2/fam_size;

***************************
Value of medigap
***************************;
pc_medigap=medigap*tot_medigap/fam_size;

***************************
Value of medicaid
***************************;

IF perslt18 GT 0 AND persot64 GT 0 THEN DO;
   IF tot_medicaid NE . THEN
   pc_medicaid=(mcaid_child*MIN(perslt18,tot_medicaid)
		+mcaid_adults*MAX(0,MIN(adults,tot_medicaid-MIN(perslt18,tot_medicaid)))
		+mcaid_aged*MAX(0,MIN(persot64,tot_medicaid-MIN(perslt18,tot_medicaid)-MIN(adults,tot_medicaid))))/fam_size;
END;
IF perslt18 GT 0 AND persot64=0 THEN DO;
   IF tot_medicaid NE . THEN
   pc_medicaid=(mcaid_child*MIN(perslt18,tot_medicaid)
		+mcaid_adults*MAX(0,MIN(adults,tot_medicaid-MIN(perslt18,tot_medicaid))))/fam_size;
END;
IF perslt18=0 AND persot64 GT 0 THEN DO;
   IF tot_medicaid NE . THEN
   pc_medicaid=(mcaid_aged*MIN(persot64,tot_medicaid)
		+mcaid_adults*MAX(0,MIN(adults,tot_medicaid-MIN(persot64,tot_medicaid))))/fam_size;
END;
IF perslt18=0 AND persot64=0 THEN DO;
   IF tot_medicaid NE . THEN
   pc_medicaid=mcaid_adults*MIN(tot_medicaid,adults)/fam_size;
END;

* If medicaid is missing then delete obs, but if private care is missing then keep. 
  Less than 1% are missing;
IF pc_medicaid NE . THEN pc_total_insurance=SUM(OF pc_medicaid pc_private pc_medicare pc_medigap);


KEEP newid srv_year medicare mcaid_aged mcaid_adults mcaid_child empl_health_ind_3yravg
       empl_health_fam_3yravg pc_medicaid pc_private pc_medicare 
	tot_private tot_medicaid tot_medicare2 num_ind_plan num_fam_plan fsize_bar pc_medigap
	tot_private2 state2 num_ind_plan2 num_fam_plan2 medigap tot_medigap pc_total_insurance mdcdenr;
RUN;

PROC MEANS;
RUN;


************************************************************************
MERGE HEALTH STUFF WITH FULL MASTER DATA 
************************************************************************;
PROC SORT data=alldat.cons_exp80_17; 
   BY newid; 
RUN; 
PROC SORT data=master_temp1; 
   BY newid; 
RUN; 

DATA cons_exp80_17_plus;
   MERGE alldat.cons_exp80_17(IN=in1) master_temp1;
   BY newid;
   IF in1;
RUN;

PROC MEANS DATA=cons_exp80_17_plus;
RUN;

